package de.feedreader.bundesliga;
import java.io.File;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.util.Log;

public class DatabaseHandler extends SQLiteOpenHelper {

	


	// All Static variables
	// Database Version
	private static final int DATABASE_VERSION = 1;

	// Database Name
	private static final String DATABASE_NAME = "Tippspiel";

	// Contacts table name
	private static final String TABLE_CONTACTS = "test1";

	// Contacts Table Columns names
	private static final String KEY_ID = "id";
	private static final String KEY_PARTIE = "Partie";
	private static final String KEY_ERG = "Ergebnis";
	private static final String KEY_HOME = "Home";
	private static final String KEY_GAST = "Gast";
	private static final String KEY_DAY = "day";
	private Context contexttemp;
	SQLiteDatabase db;
	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		contexttemp = context;
	}
	public static final String  DATABASE_FILE_PATH      = Environment.getExternalStorageDirectory().getAbsolutePath();
	private final String TAG = FeedReaderActivity.class.getSimpleName();
	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase dbtemp) {
		db = dbtemp;
		String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("
				+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_PARTIE + " TEXT,"
				+ KEY_ERG + " TEXT" + KEY_HOME + " INT"  + KEY_GAST + " INT" + KEY_DAY + " INT" + ")";
		db.openDatabase(DATABASE_FILE_PATH
	            + File.separator + DATABASE_NAME, null,SQLiteDatabase.OPEN_READWRITE);
		Log.d(TAG, "Save: " + DATABASE_FILE_PATH+ File.separator + DATABASE_NAME);
		db.execSQL(CREATE_CONTACTS_TABLE);
	}

	// Upgrading database
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);

		// Create tables again
		onCreate(db);
	}
	
	// Adding new contact 
	public void addErgebnisse(Ergebnisse ergebnisse) {
		  SQLiteDatabase db = this.getWritableDatabase(); 
		  
	        ContentValues values = new ContentValues(); 
	        values.put(KEY_PARTIE, ergebnisse.getpartie()); // Contact Name 
	        values.put(KEY_ERG, ergebnisse.getergebnisse()); // Contact Phone 
	        values.put(KEY_HOME, ergebnisse.gethome()); // Contact Name 
	        values.put(KEY_GAST, ergebnisse.getgast()); // Contact Phone 
//	        values.put(KEY_DAY, ergebnisse.getday()); // Contact Phone 
	        // Inserting Row 
	        db.insert(TABLE_CONTACTS, null, values); 
	        db.close(); // Closing database connection 
	} 
	
	public SQLiteDatabase getReadableDatabase()
	{
		db = SQLiteDatabase.openDatabase(DATABASE_FILE_PATH
	            + File.separator + DATABASE_NAME, null,
	            SQLiteDatabase.OPEN_READONLY);
	    return db;
	}

	public SQLiteDatabase getWritableDatabase()
	{
		db = SQLiteDatabase.openDatabase(DATABASE_FILE_PATH
	            + File.separator + DATABASE_NAME, null,
	            SQLiteDatabase.OPEN_READWRITE);
	    return db;
	}
	  
	// Getting single contact 
	public Ergebnisse getErgebnisse(int id) {
	    SQLiteDatabase db = this.getReadableDatabase(); 
	    
        Cursor cursor = db.query(TABLE_CONTACTS, new String[] { KEY_ID, 
        		KEY_PARTIE, KEY_ERG }, KEY_ID + "=?", 
                new String[] { String.valueOf(id) }, null, null, null, null); 
        if (cursor != null) 
            cursor.moveToFirst(); 
  
        Ergebnisse ergebnisse = new Ergebnisse(Integer.parseInt(cursor.getString(0)), 
                cursor.getString(1), cursor.getString(2), cursor.getInt(3), cursor.getInt(4), cursor.getInt(5) ); 
        // return contact 
        return ergebnisse; 
		
	} 
	  
	// Getting All Contacts 
	public List<Ergebnisse> getAllErgebnisse( ) {
		
		   List<Ergebnisse> ergebnisseList = new ArrayList<Ergebnisse>(); 
		    // Select All Query 
		    String selectQuery = "SELECT  * FROM " + TABLE_CONTACTS; 
		 
		    SQLiteDatabase db = this.getReadableDatabase();
	
		    Cursor cursor = db.rawQuery(selectQuery, null); 
		 
		    // looping through all rows and adding to list 
		    if (cursor.moveToFirst()) { 
		        do { 
		        	Ergebnisse ergebnisse = new Ergebnisse(); 
		        	ergebnisse.setid(Integer.parseInt(cursor.getString(0))); 
		        	ergebnisse.setpartie(cursor.getString(1)); 
		        	ergebnisse.setergebnisse(cursor.getString(2)); 
		            // Adding contact to list 
		        	ergebnisseList.add(ergebnisse); 
		        } while (cursor.moveToNext()); 
		    } 
		 
		    // return contact list 
		    return ergebnisseList; 
		
	} 
	  
	// Getting contacts Count 
	public int getContactsCount() {
		String countQuery = "SELECT  * FROM " + TABLE_CONTACTS; 
        SQLiteDatabase db = this.getReadableDatabase(); 
        Cursor cursor = db.rawQuery(countQuery, null); 
        cursor.close(); 
  
        // return count 
        return cursor.getCount(); 
	} 
	// Updating single contact 
	public int updateContact(Ergebnisse ergebnisse) {
		SQLiteDatabase db = this.getWritableDatabase(); 
		 
	    ContentValues values = new ContentValues(); 
	    values.put(KEY_PARTIE, ergebnisse.getpartie()); 
	    values.put(KEY_ERG, ergebnisse.getergebnisse()); 
	 
	    // updating row 
	    return db.update(TABLE_CONTACTS, values, KEY_ID + " = ?", 
	            new String[] { String.valueOf(ergebnisse.getid()) }); 
	} 
	  
	// Deleting single contact 
	public void deleteContact(Ergebnisse ergebnisse) {
		   SQLiteDatabase db = this.getWritableDatabase(); 
		    db.delete(TABLE_CONTACTS, KEY_ID + " = ?", 
		            new String[] { String.valueOf(ergebnisse.getid()) }); 
		    db.close(); 
	}
}
